/*
 * Copyright (c) 2011-2023, ThinkWide (cnet2001@163.com).
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package com.thinkwide.data.ormapping.db;

import com.thinkwide.data.config.GlobalConfig;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Arrays;
import java.util.Calendar;
import java.util.Date;

/**
 * 数据管理器 主要对原生java数据库对象的封装
 *
 * @author AndyZhang 2011
 * @since 2010-12-29
 */
public class MFDataManager {

    /**
     * @return
     */
    private static Logger logger = LoggerFactory.getLogger(MFDataManager.class);

    private static boolean isCheck = false;

    public static MFConnectionInfo getConnectionInfo() {
        try {
            return MFConnectionPool.getInstance().getConnectionInfo();
        } catch (Throwable ex) {
            logger.error(ex.getMessage(), ex);
            return null;
        }
    }

    /**
     * @param name
     * @return
     */
    public static MFConnectionInfo getConnectionInfo(String name) {
        try {
            return MFConnectionPool.getInstance().getConnectionInfo(name);
        } catch (Throwable ex) {
            logger.error(ex.getMessage(), ex);
            return null;
        }

    }

    /**
     * 获取一个数据库连接
     *
     * @return 数据库连接
     * @throws Throwable
     */
    public static Connection getConnection(String name) {
        Connection conn = null;
        try {
            conn = MFConnectionPool.getInstance().getConnection(name);

        } catch (Throwable ex) {
            logger.error(ex.getMessage(), ex);
        }

        return conn;
    }


    /**
     * 获取一个数据库连接
     *
     * @return 数据库连接
     */

    public static Connection getConnection() {
        Connection conn = null;
        try {
            conn = MFConnectionPool.getInstance().getConnection();
            if (conn == null) {
                logger.error("Wait for Start DB Pool.");
            }

        } catch (Throwable ex) {
            logger.error(ex.getMessage(), ex);
        }

        return conn;
    }


    /**
     * 获取一个 Statement
     * 该 Statement 已经设置数据集 可以滚动,可以更新
     *
     * @return 如果获取失败将返回 null,调用时记得检查返回值
     */
    public static Statement getStatement() {
        Connection conn = getConnection();
        if (conn == null) {
            return null;
        }
        try {
            return conn.createStatement();
            //ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE
            // 设置数据集可以滚动,可以更新
        } catch (SQLException ex) {
            logger.error(ex.getMessage(), ex);

            close(conn);
        }
        return null;
    }

    /**
     * 获取一个 Statement
     * 该 Statement 已经设置数据集 可以滚动,可以更新
     *
     * @param conn 数据库连接
     * @return 如果获取失败将返回 null,调用时记得检查返回值
     */
    public static Statement getStatement(Connection conn) {
        if (conn == null) {
            return null;
        }

        try {
            // 设置数据集可以滚动,可以更新//ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY
            return conn.createStatement();//ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE

        } catch (SQLException ex) {
            logger.error(ex.getMessage(), ex);
            return null;
        }

    }

    /**
     * 获取一个带参数的 PreparedStatement
     * 该 PreparedStatement 已经设置数据集 可以滚动,可以更新
     *
     * @param cmdText   需要 ? 参数的 SQL 语句
     * @param cmdParams SQL 语句的参数表
     * @return 如果获取失败将返回 null,调用时记得检查返回值
     */
    private static PreparedStatement getPreparedStatementExt(String cmdText, Object[] cmdParams) {
        Connection conn = getConnection();
        if (conn == null) {
            return null;
        }

        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(cmdText, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            int i = 1;
            for (Object item : cmdParams) {
                bindParams(pstmt, i, item);
                //pstmt.setObject(i, item);
                i++;
            }
        } catch (SQLException ex) {
            logger.error(ex.getMessage(), ex);
            close(conn);
        }
        return pstmt;
    }


    /**
     * 获取一个带参数的 PreparedStatement
     * 该 PreparedStatement 已经设置数据集 可以滚动,可以更新
     *
     * @param cmdText   需要 ? 参数的 SQL 语句
     * @param cmdParams SQL 语句的参数表
     * @return 如果获取失败将返回 null,调用时记得检查返回值
     */
    public static PreparedStatement getPreparedStatement(String cmdText, Object... cmdParams) {
        Connection conn = getConnection();
        if (conn == null) {
            return null;
        }

        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(cmdText, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            int i = 1;
            for (Object item : cmdParams) {
                bindParams(pstmt, i, item);
                //pstmt.setObject(i, item);
                i++;
            }
        } catch (SQLException ex) {
            logger.error(ex.getMessage(), ex);
            close(conn);
        }
        return pstmt;
    }


    /**
     * 获取一个带参数的 PreparedStatement
     * 该 PreparedStatement 已经设置数据集 可以滚动,可以更新
     *
     * @param conn      数据库连接
     * @param cmdText   需要 ? 参数的 SQL 语句
     * @param cmdParams SQL 语句的参数表
     * @return 如果获取失败将返回 null,调用时记得检查返回值
     * @throws SQLException
     */
    public static PreparedStatement getPreparedStatement(Connection conn, String cmdText, Object... cmdParams) throws SQLException {
        if (conn == null) {
            return null;
        }

        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(cmdText, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            int i = 1;
            for (Object item : cmdParams) {
                //pstmt.setObject(i, item);
                bindParams(pstmt, i, item);
                i++;
            }
        } catch (SQLException ex) {
            logger.error(ex.getMessage(), ex);
            close(pstmt);
        }

        return pstmt;
    }


    public static void bindParams(PreparedStatement stmt, int index, Object value) throws SQLException {
        if (value == null) {
            stmt.setObject(index, null);
        } else if (String.class.equals(value.getClass())) {
            stmt.setString(index, (String) value);
        } else if (Integer.class.equals(value.getClass())) {
            stmt.setInt(index, (Integer) value);
        } else if (Long.class.equals(value.getClass())) {
            stmt.setLong(index, (Long) value);
        } else if (Double.class.equals(value.getClass())) {
            stmt.setDouble(index, (Double) value);
        } else if (Float.class.equals(value.getClass())) {
            stmt.setFloat(index, (Float) value);
        } else if (value instanceof Calendar) {
            long inMillis = ((Calendar) value).getTimeInMillis();
            stmt.setTimestamp(index, new Timestamp(inMillis));
        } else if (value instanceof Date) {
            long time = ((Date) value).getTime();
            stmt.setTimestamp(index, new Timestamp(time));
        } else if (Boolean.class.equals(value.getClass())) {
            stmt.setBoolean(index, (Boolean) value);
        } else if (Byte.class.equals(value.getClass())) {
            stmt.setByte(index, (Byte) value);
        } else if (Short.class.equals(value.getClass())) {
            stmt.setShort(index, (Short) value);
        } else if (Character.class.equals(value.getClass())) {
            stmt.setString(index, value.toString());
        } else if (BigDecimal.class.equals(value.getClass())) {
            stmt.setBigDecimal(index, (BigDecimal) value);
        } else {
            stmt.setObject(index, value);
        }
    }

    /**
     * 执行 SQL 语句,返回结果为整型
     * 主要用于执行非查询语句
     *
     * @param cmdText SQL 语句
     * @return 非负数:正常执行; -1:执行错误; -2:连接错误
     * @throws SQLException
     */
    public static int execSql(String cmdText) throws SQLException {
        Statement stmt = getStatement();
        if (stmt == null) {
            return -2;
        }
        int i;
        try {
            GlobalConfig.show_sql(cmdText);
            i = stmt.executeUpdate(cmdText);
        } catch (SQLException ex) {
            logger.error(ex.getMessage(), ex);
            i = -1;
            closeStatement(stmt);
            throw ex;
        }
        closeStatement(stmt);
        return i;
    }

    /**
     * 执行 SQL 语句,返回结果为整型
     * 主要用于执行非查询语句
     *
     * @param cmdText SQL 语句
     * @return 非负数:正常执行; -1:执行错误; -2:连接错误
     * @throws SQLException
     */
    public static int execSql(Connection conn, String cmdText) throws SQLException {
        Statement stmt = getStatement(conn);
        if (stmt == null) {
            return -2;
        }
        int i;
        try {
            GlobalConfig.show_sql(cmdText);
            i = stmt.executeUpdate(cmdText);

        } catch (SQLException ex) {
            logger.error(ex.getMessage(), ex);
            i = -1;
            close(stmt);

            throw ex;
        }
        close(stmt);
        return i;
    }

    /**
     * 执行 SQL 语句,返回结果为整型
     * 主要用于执行非查询语句
     *
     * @param cmdText   需要 ? 参数的 SQL 语句
     * @param cmdParams SQL 语句的参数表
     * @return 非负数:正常执行; -1:执行错误; -2:连接错误
     * @throws SQLException
     */
    public static int execSql(String cmdText, Object... cmdParams) throws SQLException {
        PreparedStatement pstmt = getPreparedStatement(cmdText, cmdParams);
        if (pstmt == null) {
            return -2;
        }
        int i;

        try {
            GlobalConfig.show_sql(cmdText, Arrays.asList(cmdParams));
            i = pstmt.executeUpdate();
        } catch (SQLException ex) {
            logger.error(ex.getMessage(), ex);
            i = -1;

            closePreparedStatement(pstmt);
            throw ex;
        }

        closePreparedStatement(pstmt);
        return i;
    }

    /**
     * 执行 SQL 语句,返回结果为整型
     * 主要用于执行非查询语句
     *
     * @param conn      数据库连接
     * @param cmdText   需要 ? 参数的 SQL 语句
     * @param cmdParams SQL 语句的参数表
     * @return 非负数:正常执行; -1:执行错误; -2:连接错误
     * @throws SQLException
     */
    public static int execSql(Connection conn, String cmdText, Object... cmdParams) throws SQLException {
        PreparedStatement pstmt = getPreparedStatement(conn, cmdText, cmdParams);
        if (pstmt == null) {
            return -2;
        }
        int i;
        try {
            GlobalConfig.show_sql(cmdText, Arrays.asList(cmdParams));
            i = pstmt.executeUpdate();

        } catch (SQLException ex) {
            logger.error(ex.getMessage(), ex);
            i = -1;

            close(pstmt);
            throw ex;
        }
        close(pstmt);
        return i;
    }


    /**
     * 执行存储过程 语句,返回结果为ResultSet
     *
     * @param conn      数据库连接
     * @param cmdText   需要 ? 参数的 SQL 语句  "{? = call ageofstu(?)}";
     * @param cmdParams SQL 语句的参数表
     * @return ResultSet或null (错误)
     * @throws SQLException 未完成待扩展...
     */
    public static ResultSet callSpQuery(Connection conn, String cmdText, Object... cmdParams) throws SQLException {
        CallableStatement cs = conn.prepareCall(cmdText);
        ResultSet rs = null;
        if (cs == null) {
            return rs;
        }

        try {
            int i = 0;
            if (cmdParams != null) {
                for (Object item : cmdParams) {
                    i++;
                    if (item instanceof String) {
                        cs.setString(i, item == null ? null : (String) item);
                    }
                    ;
                    if (item instanceof Integer) {
                        cs.setInt(i, item == null ? null : (Integer) item);
                    }
                    ;
                    if (item instanceof Date) {
                        cs.setDate(i, (item == null ? null : (java.sql.Date) item));
                    }
                    ;
                    if (item instanceof Double) {
                        cs.setDouble(i, item == null ? null : (Double) item);
                    }
                    ;
                    if (item instanceof Float) {
                        cs.setFloat(i, item == null ? null : (Float) item);
                    }
                    ;
                    if (item instanceof Boolean) {
                        cs.setBoolean(i, item == null ? null : (Boolean) item);
                    }
                    ;
                }
            }
            GlobalConfig.show_sql(cmdText, Arrays.asList(cmdParams));
            rs = cs.executeQuery();

        } catch (SQLException ex) {
            logger.error(ex.getMessage(), ex);
            cs.close();
            throw ex;
        }

        return rs;
    }

    public static Object callSpReturnLast(Connection conn, String cmdText, int returnType, Object... cmdParams) throws SQLException {
        CallableStatement cs = conn.prepareCall(cmdText);
        boolean rs = true;
        if (cs == null) {
            return false;
        }

        try {
            int i = 0;

            if (cmdParams != null) {
                for (Object item : cmdParams) {
                    i++;
                    if (item instanceof String) {
                        cs.setString(i, item == null ? null : (String) item);
                    }
                    ;
                    if (item instanceof Integer) {
                        cs.setInt(i, item == null ? null : (Integer) item);
                    }
                    ;
                    if (item instanceof Date) {
                        cs.setDate(i, (item == null ? null : (java.sql.Date) item));
                    }
                    ;
                    if (item instanceof Double) {
                        cs.setDouble(i, item == null ? null : (Double) item);
                    }
                    ;
                    if (item instanceof Float) {
                        cs.setFloat(i, item == null ? null : (Float) item);
                    }
                    ;
                    if (item instanceof Boolean) {
                        cs.setBoolean(i, item == null ? null : (Boolean) item);
                    }
                    ;
                }
            }
            i++;
            cs.registerOutParameter(i, returnType);
            GlobalConfig.show_sql(cmdText, Arrays.asList(cmdParams));
            cs.execute();
            return cs.getObject(i);

        } catch (SQLException ex) {
            logger.error(ex.getMessage(), ex);
            cs.close();
            throw ex;
        }

    }

    public static Object callSpReturn(Connection conn, String cmdText, int returnType, Object... cmdParams) throws SQLException {
        CallableStatement cs = conn.prepareCall(cmdText);
        boolean rs = true;
        if (cs == null) {
            return false;
        }

        try {
            int i = 0;
            i++;
            cs.registerOutParameter(1, returnType);
            if (cmdParams != null)
                for (Object item : cmdParams) {
                    i++;
                    if (item instanceof String) {
                        cs.setString(i, item == null ? null : (String) item);
                    }
                    ;
                    if (item instanceof Integer) {
                        cs.setInt(i, item == null ? null : (Integer) item);
                    }
                    ;
                    if (item instanceof Date) {
                        cs.setDate(i, (item == null ? null : (java.sql.Date) item));
                    }
                    ;
                    if (item instanceof Double) {
                        cs.setDouble(i, item == null ? null : (Double) item);
                    }
                    ;
                    if (item instanceof Float) {
                        cs.setFloat(i, item == null ? null : (Float) item);
                    }
                    ;
                    if (item instanceof Boolean) {
                        cs.setBoolean(i, item == null ? null : (Boolean) item);
                    }
                    ;
                }
            GlobalConfig.show_sql(cmdText, Arrays.asList(cmdParams));
            cs.execute();
            return cs.getObject(1);

        } catch (SQLException ex) {
            logger.error(ex.getMessage(), ex);
            cs.close();
            throw ex;
        }

    }

    public static boolean callSp(Connection conn, String cmdText, Object... cmdParams) throws SQLException {
        CallableStatement cs = conn.prepareCall(cmdText);
        boolean rs = true;
        if (cs == null) {
            return false;
        }

        try {
            int i = 0;
            if (cmdParams != null) {
                for (Object item : cmdParams) {
                    i++;
                    if (item instanceof String) {
                        cs.setString(i, item == null ? null : (String) item);
                    }
                    ;
                    if (item instanceof Integer) {
                        cs.setInt(i, item == null ? null : (Integer) item);
                    }
                    ;
                    if (item instanceof Date) {
                        cs.setDate(i, (item == null ? null : (java.sql.Date) item));
                    }
                    ;
                    if (item instanceof Double) {
                        cs.setDouble(i, item == null ? null : (Double) item);
                    }
                    ;
                    if (item instanceof Float) {
                        cs.setFloat(i, item == null ? null : (Float) item);
                    }
                    ;
                    if (item instanceof Boolean) {
                        cs.setBoolean(i, item == null ? null : (Boolean) item);
                    }
                    ;
                }
            }
            GlobalConfig.show_sql(cmdText, Arrays.asList(cmdParams));
            rs = cs.execute();

        } catch (SQLException ex) {
            logger.error(ex.getMessage(), ex);
            cs.close();
            throw ex;
        }

        return rs;
    }


    /**
     * 返回结果集的第一行的一列的值,其他忽略
     *
     * @param cmdText SQL 语句
     * @return
     * @throws SQLException
     */
    public static Object execScalar(String cmdText) throws SQLException {
        ResultSet rs = getResultSet(cmdText);
        Object obj = buildScalar(rs);

        //Statement stmt=rs.getStatement();
        //Connection conn=stmt.getConnection();
        //rs.close();
        //stmt.close();
        //conn.close();
        closeResultSet(rs);
        return obj;
    }

    /**
     * 返回结果集的第一行的一列的值,其他忽略
     *
     * @param conn    数据库连接
     * @param cmdText SQL 语句
     * @return
     * @throws SQLException
     */
    public static Object execScalar(Connection conn, String cmdText) throws SQLException {
        ResultSet rs = getResultSet(conn, cmdText);
        Object obj = buildScalar(rs);
        close(rs.getStatement());
        close(rs);
        return obj;
    }

    /**
     * 返回结果集的第一行的一列的值,其他忽略
     *
     * @param cmdText   需要 ? 参数的 SQL 语句
     * @param cmdParams SQL 语句的参数表
     * @return
     * @throws SQLException
     */
    public static Object execScalar(String cmdText, Object... cmdParams) throws SQLException {
        ResultSet rs = getResultSet(cmdText, cmdParams);
        Object obj = buildScalar(rs);
        closeResultSet(rs);
        return obj;
    }

    /**
     * 返回结果集的第一行的一列的值,其他忽略
     *
     * @param conn      数据库连接
     * @param cmdText   需要 ? 参数的 SQL 语句
     * @param cmdParams SQL 语句的参数表
     * @return
     * @throws SQLException
     */
    public static Object execScalar(Connection conn, String cmdText, Object... cmdParams) throws SQLException {
        ResultSet rs = getResultSet(conn, cmdText, cmdParams);
        Object obj = buildScalar(rs);
        close(rs.getStatement());
        close(rs);
        return obj;
    }

    /**
     * 返回一个 ResultSet
     *
     * @param cmdText SQL 语句
     * @return
     * @throws SQLException
     */
    public static ResultSet getResultSet(String cmdText) throws SQLException {
        Statement stmt = getStatement();
        if (stmt == null) {
            return null;
        }

        try {
            GlobalConfig.show_sql(cmdText);
            return stmt.executeQuery(cmdText);
        } catch (SQLException ex) {
            logger.error(ex.getMessage(), ex);
            closeStatement(stmt);
            throw ex;
        }
    }

    /**
     * 返回一个 ResultSet
     *
     * @param conn
     * @param cmdText SQL 语句
     * @return
     * @throws SQLException
     */
    public static ResultSet getResultSet(Connection conn, String cmdText) throws SQLException {
        Statement stmt = getStatement(conn);
        ResultSet rs = null;
        if (stmt == null) {
            return null;
        }

        try {
            GlobalConfig.show_sql(cmdText);
            rs = stmt.executeQuery(cmdText);
        } catch (SQLException ex) {
            logger.error(ex.getMessage(), ex);
            close(stmt);
            throw ex;
        }
        return rs;

    }

    /**
     * 返回一个 ResultSet
     *
     * @param cmdText   需要 ? 参数的 SQL 语句
     * @param cmdParams SQL 语句的参数表
     * @return
     * @throws SQLException
     */
    public static ResultSet getResultSet(String cmdText, Object... cmdParams) throws SQLException {
        PreparedStatement pstmt = getPreparedStatement(cmdText, cmdParams);
        ResultSet rs = null;
        if (pstmt == null) {
            return rs;
        }

        try {
            GlobalConfig.show_sql(cmdText, Arrays.asList(cmdParams));
            rs = pstmt.executeQuery();
        } catch (SQLException ex) {
            logger.error(ex.getMessage(), ex);
            close(pstmt);
            throw ex;
        }

        return rs;
    }

    /**
     * 返回一个 ResultSet
     *
     * @param conn      数据库连接
     * @param cmdText   需要 ? 参数的 SQL 语句
     * @param cmdParams SQL 语句的参数表
     * @return
     * @throws SQLException
     */
    public static ResultSet getResultSet(Connection conn, String cmdText, Object... cmdParams) throws SQLException {
        PreparedStatement pstmt = getPreparedStatement(conn, cmdText, cmdParams);
        ResultSet rs = null;
        if (pstmt == null) {
            return rs;
        }

        try {
            GlobalConfig.show_sql(cmdText, Arrays.asList(cmdParams));
            rs = pstmt.executeQuery();
        } catch (SQLException ex) {
            logger.error(ex.getMessage(), ex);
            close(pstmt);
            throw ex;
        }

        return rs;
    }

    public static Object buildScalar(ResultSet rs) throws SQLException {
        if (rs == null) {
            return null;
        }

        Object obj = null;
        try {
            if (rs.next()) {
                obj = rs.getObject(1);
            }
        } catch (SQLException ex) {
            logger.error(ex.getMessage(), ex);
            throw ex;
        }
        return obj;
    }

    public static void close(Object obj) {
        if (obj == null) {
            return;
        }
        try {
            if (obj instanceof Statement) {
                ((Statement) obj).close();
            } else if (obj instanceof PreparedStatement) {
                ((PreparedStatement) obj).close();
            } else if (obj instanceof ResultSet) {
                ((ResultSet) obj).close();
            } else if (obj instanceof Connection) {
                ((Connection) obj).close();
            }
        } catch (SQLException ex) {
            logger.error(ex.getMessage(), ex);
        }
    }

    public static void closeResultSet(ResultSet rs) throws SQLException {
        Statement stmt = rs.getStatement();
        Connection conn = stmt.getConnection();

        close(rs);
        close(stmt);
        close(conn);

    }

    public static void closeStatement(Statement stmt) throws SQLException {
        Connection conn = stmt.getConnection();

        close(stmt);
        close(conn);

    }

    public static void closePreparedStatement(PreparedStatement pstmt) throws SQLException {
        Connection conn = pstmt.getConnection();
        close(pstmt);
        close(conn);

    }

}
